package com.zjl.daoImpl;


import java.awt.List;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Scanner;

import com.util.DBManager;
import com.util.QueryDataResult;
import com.zjl.entity.StudentBean;


public class StudentDaoImpl {
    StudentBean[] sBeans=new StudentBean[50];//数组添加位置
    Scanner scanner=new Scanner(System.in);
	//显示所有学生
	public void showALL() {
		ArrayList<StudentBean> aList=new ArrayList<StudentBean>();
		String sqlString="select sid,sname,sex,age,sno,classid from student";
		QueryDataResult rSet=DBManager.excuteQuery(sqlString);
			if (rSet!=null) {
				try {
					ResultSet rsSet=rSet.rs;
					while (rsSet.next()) {
						aList.add(new StudentBean(rsSet.getInt("sid"),rsSet.getString("sname"),rsSet.getString("sex"),
								rsSet.getInt("age"),rsSet.getInt("sno"),rsSet.getInt("classid")));
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			rSet.close();
			System.out.println("姓名\t班级id\t性别\t年龄\t学籍号\t学号");
			for (StudentBean pBean : aList) {
				System.out.println(pBean.getSname()+"\t"+pBean.getClassid()+"\t"+pBean.getSex()+"\t"
				+pBean.getSno()+"\t"+pBean.getAge()+"\t"+pBean.getSid());
			}
	}
	
	//判断sid是否存在
		public StudentBean selectById(int sid) {
			String sql="select sid,sname,sex,age,sno,classid from student where sid="+sid;
		    QueryDataResult queryDataResult =DBManager.excuteQuery(sql);
		    try {
		    	ResultSet rsSet=queryDataResult.rs;
				if (rsSet.next()) {
					//System.out.println("sid重复");
					return new StudentBean(rsSet.getInt("sid"),rsSet.getString("sname"),rsSet.getString("sex"),
							rsSet.getInt("age"),rsSet.getInt("sno"),rsSet.getInt("classid"));
				}
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		    return null;
		}
	//添加
	public void addStudent() {
		System.out.println("学生编号");
	    int sid=scanner.nextInt();
	    StudentBean pBean=selectById(sid);
	    if (pBean!=null) {
			System.out.println("学生编号重复,请重新添加");
		}else {
			    System.out.println("学生姓名");
			    String sname=scanner.next();
			    System.out.println("学生性别");
			    String sex=scanner.next();
			    System.out.println("学生年龄");
			    String age=scanner.next();
			    System.out.println("学籍");
			    String sno=scanner.next();
			    System.out.println("班级id");
			    String classid=scanner.next();
			    String sqlString="insert into student(sid,sname,sex,age,sno,classid) values('"+sid+"','"+sname+"','"+sex+"','"+age+"','"+sno+"','"+classid+"')";
			    int result=DBManager.excuteUpdate(sqlString);
			    System.out.println(result>0?"添加成功":"添加失败");
		}
	    
	}
	//修改
	
	public void StudentUpdate() {
		System.out.println("请输入要改的学生编号");
		int sid=scanner.nextInt();
		StudentBean pBean=selectById(sid);
		if (pBean==null) {
			System.out.println("学生编号不存在,无法修改");
		}else {
			String sname=pBean.getSname();
			String sex=pBean.getSex();
			int age=pBean.getAge();
			int sno=pBean.getSno();
			int classid=pBean.getClassid();
			System.out.println("是否修改姓名 y/n");
			String str=scanner.next();
			if (str.equals("y")) {
				System.out.println("请输入需要修改的学生姓名");
				sname=scanner.next();
			}
			System.out.println("是否修改性别 y/n");
			str=scanner.next();
			if (str.equals("y")) {
				System.out.println("请输入需要修改的学生性别");
				sex=scanner.next();
			}
			System.out.println("是否修改年龄 y/n");
			str=scanner.next();
			if (str.equals("y")) {
				System.out.println("请输入需要修改的年龄");
				age=scanner.nextInt();
			}
			System.out.println("是否修改学籍号 y/n");
			str=scanner.next();
			if (str.equals("y")) {
				System.out.println("请输入需要修改的学籍号");
				sno=scanner.nextInt();
			}
			System.out.println("是否修改班级id y/n");
			str=scanner.next();
			if (str.equals("y")) {
				System.out.println("请输入需要修改的班级id");
				classid=scanner.nextInt();
			}
			String sqlString="UPDATE student SET sname='"+sname+"',sex='"+sex+"',sno='"+sno
					+"',age='"+age+"',classid='"+classid+"' WHERE sid='"+sid+"'";
			int result=DBManager.excuteUpdate(sqlString);
			System.out.println(result>0?"修改成功":"修改失败");
		}
		
	}
	//判断pname是否存在
			public StudentBean selectById1(String sname) {
				String sql="select sid,sname,sex,age,sno,classid from student where sname='"+sname+"'";
			    QueryDataResult queryDataResult =DBManager.excuteQuery(sql);
			    try {
			    	ResultSet rsSet=queryDataResult.rs;
					if (rsSet.next()) {
						return new StudentBean(rsSet.getInt("sid"),rsSet.getString("sname"),rsSet.getString("sex"),
								rsSet.getInt("age"),rsSet.getInt("sno"),rsSet.getInt("classid"));
					}
				} catch (Exception e) {
					// TODO: handle exception
					e.printStackTrace();
				}
			    return null;
			}
		
		//删除
		public void deleteStudent() {
			System.out.println("请输入要删除的学生姓名");
			String sname=scanner.next();
			StudentBean pBean=selectById1(sname);
		    if (pBean==null) {
				System.out.println("该学生不存在,无法删除");
			}else {
				String sqlString="DELETE from student WHERE sname='"+sname+"'";
				int result=DBManager.excuteUpdate(sqlString);
				System.out.println(result>0?"删除成功":"删除失败");
			}
			}

}
